/**
 * @author zhounan 
 * @date 2024年5月22日
 * @version V1.0
 */
package org.zn.etl.jdbc;

import java.util.List;
import java.util.Set;

import org.zn.etl.job.context.GlobalContextKey;

import com.google.common.collect.Lists;
import com.google.common.collect.Sets;

/**
 * @author zhounan 
 * @date 2024年5月22日
 * @version V1.0
 */
public class SqlPagedTemplate {
	
	
	public static String oracleSubSeclt(String sql) {
		
		StringBuilder sb=new StringBuilder();
		sb.append("SELECT * FROM  (  "
				+ "	SELECT  page.*,ROWNUM rnum  FROM  "
				+ "(");
		sb.append(sql);
		sb.append(" ) page  WHERE ROWNUM <= ${#"+GlobalContextKey.END_OFFSET+"} ) "
				+ "WHERE rnum > ${#"+GlobalContextKey.START_OFFSET+"}");
		return sb.toString();
		
		
	}
	
	
	public static String oracle(String table, List<String> items) {
		
		StringBuilder sb=new StringBuilder();
		sb.append("SELECT * FROM  (  "
				+ "	SELECT  page.*,ROWNUM rnum  FROM  "
				+ "		( SELECT ");
		
		for (String it : items) {
			
			
			
			sb.append(it).append(",");
		}
		
		sb.append("ROWNUM rno ").append(" FROM ").append( table);
		
		sb.append(" ) page  WHERE ROWNUM <= ${#"+GlobalContextKey.END_OFFSET+"} ) "
				+ "WHERE rnum > ${#"+GlobalContextKey.START_OFFSET+"}");
		return sb.toString();
	}
	
	
	public static String mysql(String table, List<String> items) {
	
		StringBuilder  sb=new StringBuilder();
		sb.append("SELECT");
		for (String item : items) {
		
			sb.append(item).append(",");
			
		}
		
		sb.deleteCharAt(sb.length());
		sb.append(" FROM ").append(table).append("limit ${#"+GlobalContextKey.START_OFFSET+"},${#"+GlobalContextKey.PAGED_SIZE+"}") ;
		
		return table;
	
	}
	
	public static String postgresql(String table, List<String> items) {
		
		StringBuilder  sb=new StringBuilder();
		sb.append("SELECT");
		for (String item : items) {
		
			sb.append(item).append(",");
			
		}
		
		sb.deleteCharAt(sb.length());
		sb.append(" FROM ").append(table).append("LIMIT  ${#"+GlobalContextKey.PAGED_SIZE+"} OFFSET  ${#"+GlobalContextKey.START_OFFSET+"}") ;
		
		return table;
	
	}
	
	public static void main(String[] args) {
		
		String sql= SqlPagedTemplate.oracleSubSeclt("SELECT a.* , oper.CREATEDATE, oper.CREATORID, oper.UPDATEDATE, oper.UPDATERID, ROWNUM rno FROM B_ATTACHINFO a LEFT JOIN B_OPERATORINFO oper ON a.REGISTRATIONID = oper.REGISTRATIONID ");
		
		System.out.println(sql);
		
		
	}
	
	
}
